package com.huazi.www.drug.drugz.dao.impl;


import com.huazi.www.drug.drugz.dao.DrugDao;
import com.huazi.www.drug.drugz.enity.ApplyLog;
import com.huazi.www.drug.drugz.enity.Drug;
import com.huazi.www.drug.drugz.enity.DrugStock;
import com.huazi.www.drug.drugz.enity.Medicate;
import com.huazi.www.drug.drugz.util.BaseDao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @description：药品dao的实现
 * @author：zt
 * @date：2021/5/8 10:16
 * @versions：v1.0
 */
public class DrugDaoImpl extends BaseDao implements DrugDao {

    /**
     * 查询药品编号、名称  绑定下拉框
     *
     * @return list集合
     */
    @Override
    public List<Drug> list() {
        List<Drug> list = new ArrayList<>();    //list集合
        String sql = "select id,drugname from drug_remedy";    //查询药品编号和药品名称的sql
        ResultSet set = executeQuery(sql);     //查询数据
        try {
            while (set.next()) {    //处理数据
                int id = set.getInt("id");
                String name = set.getString("drugname");
                Drug drug = new Drug(id, name);    //药品对象
                list.add(drug);        //集合保存对象
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {     //关闭结果集
            try {
                set.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            Close(rs, ps, conn);    //父类关闭方法
        }
        return list;    //返回药品集合
    }

    /**
     * 查询药品编号、名称、数量  检查库存
     *
     * @return list
     */
    @Override
    public List<Drug> drugs() {
        List<Drug> drugs = new ArrayList<>();    //list集合
        String sql = "select id,drugname,count from drug_remedy where count <= 100";   //查询库存小于一百的药品
        ResultSet set = executeQuery(sql);    //查询数据
        try {
            while (set.next()) {    //处理数据
                int id = set.getInt(1);
                String drugName = set.getString(2);
                int count = set.getInt(3);
                Drug drug = new Drug(id, drugName, count);     //药品对象
                drugs.add(drug);     //集合保存药品
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {    //关闭结果集
            try {
                set.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            Close(rs, ps, conn);    //父类关闭方法
        }
        return drugs;    //药品集合
    }

    //根据药品编号查询价格  放弃
//    @Override
//    public double price(int id) {
//        double price = 0;
//        String sql = "select price from drug_remedy where id = ?;";
//        rs = executeQuery(sql, id);
//        try {
//            rs.next();
//            price = rs.getDouble(1);
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        } finally {
//            Close(rs, ps, conn);
//        }
//        return price;
//    }


    //根据药品名称查询药品编号  以放弃
//    @Override
//    public int selDrug(String drugName) {
//        int drugId = 0;
//        String sql = "select id from drug_remedy where drugname = ?";
//        rs = executeQuery(sql, drugName);
//        try {
//            rs.next();
//            drugId = rs.getInt(1);
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        } finally {
//            Close(rs, ps, conn);
//        }
//        return drugId;
//    }

    /**
     * 新增添加库存申请  新增申请记录
     *
     * @param medicate 增加库存
     * @return 1 新增成功
     */
    @Override
    public int inStore(Medicate medicate) {
        String sql = "insert into drug_dosing(applycat,applytime,drugspecies,type,applystatus,remark) values(?,?,?,?,?,?)";
        return executeUpdate(sql, medicate.getName(), medicate.getDate(), medicate.getDrugSpecies(), medicate.getType(), medicate.getApplyStatus(), medicate.getRemark());

    }

    /**
     * 新增新药申请     新增申请记录
     *
     * @param medicate 新增药品
     * @return 1  新增成功
     */
    @Override
    public int addDrug(Medicate medicate) {
        String sql = "insert into drug_dosing(applycat,applytime,applydrug,drugtype,price,count,type,applystatus,remark) values(?,?,?,?,?,?,?,?,?);";
        return executeUpdate(sql, medicate.getName(), medicate.getDate(), medicate.getApplyDrug(), medicate.getDrugType(), medicate.getPrice()
                , medicate.getCount(), medicate.getType(), medicate.getApplyStatus(), medicate.getRemark());
    }

    /**
     * 查询总记录数   查询记录数
     *
     * @param xuTime 选择的查询时间端
     * @param type   申请类型
     * @return 总计录数
     */
    @Override
    public int totalCount(int xuTime, int type) {   //2
        int totalCount = 0;     //定义总记录数

        StringBuilder sql = new StringBuilder();    //StringBuilder拼接sql语句

        sql.append("select count(1) from drug_dosing where applystatus <> 2 ");    //查询申请状态为2的申请记录

        if (xuTime == 1) {    //根据时间类型查询记录数  1、2、3  between adddate(current_date,-7) and now()
            sql.append(" and applytime between ADDDATE(CURRENT_DATE,-7) and now()");
        } else if (xuTime == 2) {
            sql.append(" and applytime between ADDDATE(CURRENT_DATE,-30) and now()");
        } else if (xuTime == 3) {
            sql.append(" and applytime between ADDDAte(CURRENT_DATE,-365) and now()");
        }
        if (type == 1) {     //类型查询  1、2  【库|新】
            sql.append(" and type = '新增药品'");
        } else if (type == 2) {
            sql.append(" and type = '补充库存'");
        }

        rs = executeQuery(sql.toString());   //执行sql查询数据

        try {    //处理结果集
            rs.next();
            totalCount = rs.getInt(1);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Close(rs, ps, conn);    //父类关闭方法
        }
        return totalCount;    //返回总记录数
    }

    /**
     * 查询每页的数据  申请记录
     */
    @Override
    public List<Medicate> list(int xuTime, int type, int start, int pageSize) {   //2
        List<Medicate> list = new ArrayList<>();    //list集合

        StringBuilder sql = new StringBuilder();    //StringBuilder拼接sql语句
        sql.append("select id,applycat,applytime,drugspecies,applydrug,drugtype,price,count,type,applystatus,remark from drug_dosing where applystatus <> 2");
        //查询时间段判断
        if (xuTime == 1) {    //根据时间类型查询记录数  1、2、3  between adddate(current_date,-7) and now()
            sql.append(" and applytime between ADDDATE(CURRENT_DATE,-7) and now()");
        } else if (xuTime == 2) {
            sql.append(" and applytime between ADDDATE(CURRENT_DATE,-30) and now()");
        } else if (xuTime == 3) {
            sql.append(" and applytime between ADDDAte(CURRENT_DATE,-365) and now()");
        }
        //申请类型判断
        if (type == 1) {     //类型查询  1、2  【库|新】
            sql.append(" and type = '补充库存'");
        } else if (type == 2) {
            sql.append(" and type = '新增药品'");
        }

        sql.append(" limit ?,?");     //limit分页查询

        rs = executeQuery(sql.toString(), start, pageSize);   //执行sql查询数据

        try {
            while (rs.next()) {
                int id = rs.getInt(1);
                String name = rs.getString(2);
                Date date = rs.getTimestamp(3);
                int drugSs = rs.getInt(4);
                String drug = rs.getString(5);
                int drugType = rs.getInt(6);
                double price = rs.getDouble(7);
                int count = rs.getInt(8);
                String types = rs.getString(9);
                int status = rs.getInt(10);
                String remark = rs.getString(11);
                //申请记录 id、name、date、drugSS、drug、durgType、price、count、types、status、remark
                Medicate medicate = new Medicate(id, name, date, drugSs, drug, drugType, price, count, types, status, remark);
                list.add(medicate);     //集合保存对象
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Close(rs, ps, conn);     //父类关闭方法
        }
        return list;  //返回list
    }

    /**
     * 跟新库存药品数量
     *
     * @param drugId 药品编号
     * @param count  药品数量
     * @return 1 更新成功
     */
    @Override
    public int replenish(int drugId, int count) {
        String sql = "update drug_remedy set count = count + ? where id = ?;";
        return executeUpdate(sql, count, drugId);
    }

    //添加申请记录    以弃用
//    @Override
//    public int applyLog(ApplyLog applyLog) {
//        String sql = "insert into drug_applylog(applycat,applytime,remedyid,applytype,count,money,remark) values(?,?,?,?,?,?,?)";
//        return executeUpdate(sql, applyLog.getApplyCat(), applyLog.getDate(), applyLog.getRemedyId(),
//                applyLog.getApplyType(), applyLog.getCount(), applyLog.getMoney(), applyLog.getRemark());
//    }

    /**
     * 新增药品
     *
     * @param drug 药品
     * @return 1 增加成功
     */
    @Override
    public int addDrug(Drug drug) {
        String sql = "insert into drug_remedy(drugname,type,price,count,remark) values(?,?,?,?,?)";
        return executeUpdate(sql, drug.getDrugName(), drug.getType(), drug.getPrice(), drug.getCount(), drug.getRemark());
    }

    /**
     * 更新申请状态
     *
     * @param id 申请编号
     * @return 1 更新成功
     */
    @Override
    public int updMedicate(int id) {
        String sql = "update drug_dosing set applystatus = 2 where id = ?";
        return executeUpdate(sql, id);
    }

    /**
     * 查询总计录数  药品
     *
     * @param drugName 药品名称
     * @param drugType 药品类型
     * @return 总计录数
     */
    @Override
    public int totalCount(String drugName, int drugType) {   //3

        int count = 0;    //总记录数

        StringBuilder sql = new StringBuilder();    //StringBuilder对象

        //查询条件
        sql.append("select count(1) from drug_remedy where drugname like concat('%',?,'%')");

        //根据药品类型增加条件  drugType  0全部
        if (drugType == 0) {
            executeQuery(sql.toString(), drugName);    //直接查询
        } else {
            sql.append(" and type like concat('%',?,'%')");    //药品类型条件
            executeQuery(sql.toString(), drugName, drugType);
        }

        try {
            rs.next();
            count = rs.getInt(1);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Close(rs, ps, conn);     //父类关闭方法
        }
        return count;    //返回总记录数
    }

    //查询每页的数据 药品
    @Override
    public List<Drug> list(String drugName, int drugType, int start, int pageSize) {   //3
        List<Drug> drugs = new ArrayList<>();    //药品集合

        StringBuilder sql = new StringBuilder();  //StringBuilder对象

        //查询条件
        sql.append("select id,drugname,type,price,count,remark from drug_remedy where drugname like concat('%',?,'%')");

        //根据药品类型增加条件  drugType  0全部
        if (drugType == 0) {
            sql.append(" limit ?,?;");   //limit子句
            executeQuery(sql.toString(), drugName, start, pageSize);
        } else {
            sql.append(" and type like concat('%',?,'%')");    //药品类型条件
            sql.append(" limit ?,?;");    //limit子句
            executeQuery(sql.toString(), drugName, drugType, start, pageSize);
        }

        try {
            while (rs.next()) {
                int id = rs.getInt(1);
                String name = rs.getString(2);
                int type = rs.getInt(3);
                double price = rs.getDouble(4);
                int count = rs.getInt(5);
                String remark = rs.getString(6);
                //药品对象 id、name、type、price、count、remark
                Drug drug = new Drug(id, name, type, price, count, remark);
                drugs.add(drug);    //集合保存药品
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Close(rs, ps, conn);     //父类关闭方法
        }
        return drugs;    //返回药品集合
    }

    /**
     * 单独修改药品属性
     *
     * @param drugID    药品编号
     * @param drugField 药品字段
     * @param value     字段属性
     * @return 1 修改成功
     */
    @Override
    public int updDrug(int drugID, String drugField, String value) {
        String sql = "update drug_remedy set " + drugField + " = ?" + " where id = ?";
        return executeUpdate(sql, value, drugID);
    }

    /**
     * 添加库存药品记录
     *
     * @param drugStock 库存药品对象
     * @param date      入库时间
     * @return 1 增加成功
     */
    @Override
    public int drugStork(DrugStock drugStock, Date date) {
        String sql = "insert into drug_zz(drugid,count,date) VALUES(?,?,?)";
        return executeUpdate(sql, drugStock.getDrugId(), drugStock.getCount(), date);
    }

    /**
     * 根据时间查询同一批库存药品名单
     *
     * @param date 时间
     * @return 药品对象集合
     */
    @Override
    public List<DrugStock> list(String date) {
        List<DrugStock> list = new ArrayList<>();    //库存药品集合 DrugStock
        //sql语句
        String sql = "select a.drugid,b.drugname,b.price,a.count from drug_zz as a INNER JOIN drug_remedy as b on a.drugid = b.id where a.date = ?";
        rs = executeQuery(sql, date);
        try {
            while (rs.next()) {
                int drugId = rs.getInt(1);
                String drugName = rs.getString(2);
                double price = rs.getDouble(3);
                int count = rs.getInt(4);
                //库存药品对象  drugId、drugName、price、count
                DrugStock drugStock = new DrugStock(drugId, drugName, price, count);
                list.add(drugStock);   //集合添加 库存药品对象
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Close(rs, ps, conn);    //父类关闭方法
        }
        return list;   //返回库存药品集合
    }


}
